﻿using Business.Service;
using Model;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MvcAppWeb.Controllers
{
    public class BatchOderInfoController : BaseDataController
    {
        //
        // GET: /DeliveryBox/
        public ActionResult BatchOderInfo()
        {
            if (Session["UserInfo"] == null) return RedirectToAction("LogOn", "Account");
            else
            {
                AppUserList model = (AppUserList)Session["UserInfo"];
                return View(model);

            }
        }



        ///<summary>
        ///查询预估订单信息
        ///</summary>
        ///<returns></returns>
        public JsonResult InitJsonResult(string startDate, string endDate, string batchNo)
        {
            int page = int.Parse(Request["page"].ToString());
            int rows = int.Parse(Request["rows"].ToString());
            int total = 0;
            var retData = new object();
            BEstimateProduction db = new BEstimateProduction();
            if (batchNo == "0")
            {
                retData = db.GetDCOrderBatchDetailByAll(page, rows, out total, startDate, endDate + " 23:59").ToList();//获取分拣单信息
            }
            else
            {
                retData = db.GetDCOrderBatchDetail(page, rows, out total, startDate, endDate + " 23:59", batchNo).ToList();//获取分拣单信息
            }

            Dictionary<string, object> json = new Dictionary<string, object>();
            json.Add("total", total);
            json.Add("rows", retData);
            return Json(json);
        }


        /// <summary>
        /// 导出工厂加工单明细
        /// </summary>
        /// <param name="fjstartTime"></param>
        /// <param name="fjendTime"></param>
        /// <param name="batchNo"></param>
        /// <param name="batchName"></param>
        /// <returns></returns>
        public FileResult ExporterExcelDetail(string fjstartTime, string fjendTime, string batchNo = "0")
        {

            DateTime startdate = DateTime.Parse(fjstartTime);
            DateTime enddate = DateTime.Parse(fjendTime);
            string batchNumber = "汇总";
            int dayCount = enddate.Subtract(startdate).Days;

            BEstimateProduction db = new BEstimateProduction();
            List<ReceiveMachSchemeInfo> listReceiveMachScheme = null;
            List<OrderBatchGroupBy> listBatchGroup = null;
            if (batchNo == "0")
            {
                listReceiveMachScheme = db.GetSJDDMachSchemeByAll(fjstartTime, fjendTime + " 23:59");
                listBatchGroup = db.GetOrderBatchNumberByAll(fjstartTime, fjendTime + " 23:59").ToList();//获取分拣批次


            }
            else
            {
                batchNumber = "第" + batchNo + "批";
                listBatchGroup = db.GetOrderBatchNumber(fjstartTime, fjendTime + " 23:59", batchNo).ToList();//获取分拣批次
                listReceiveMachScheme = db.GetSJDDMachScheme(fjstartTime, fjendTime + " 23:59", batchNo);
            }

            List<BatchOrderInfo> listBatch = db.GetOrderBatchInfo(fjstartTime, fjendTime + " 23:59").ToList();//获取分拣单信息


            #region 导出Excel
            HSSFWorkbook book = new HSSFWorkbook();

            //循环查询多少天 生成多少个 sheet
            for (int i = 0; i < dayCount + 1; i++)
            {
                #region 分拣单报表

                ISheet addrBatch = book.CreateSheet(startdate.AddDays(i).ToString("yyyy-MM-dd") + "工厂分拣入库单(" + batchNumber + ")");
                ISheet addrMachScheme = book.CreateSheet("净菜加工单" + "工厂分拣入库单(" + batchNumber + ")");
                string datetime = startdate.AddDays(i).ToString("yyyy-MM-dd");

                Hashtable hshTableCell = new Hashtable(); //  创建分拣单哈希表 
                Hashtable hshTableData = new Hashtable(); //  创建加工单哈希表 

                List<OrderBatchGroupBy> listgb = listBatchGroup.Where(s => s.RATIONDATE == datetime).ToList<OrderBatchGroupBy>();

                List<BatchOrderInfo> listBatchDetail = listBatch.Where(s => s.RATIONDATE == datetime).ToList<BatchOrderInfo>();

                for (int k = 0; k < listBatchDetail.Count + 1; k++)
                {
                    addrBatch.CreateRow(k);
                    addrBatch.GetRow(k).Height = 400;
                    if (k == 0)
                    {
                        addrBatch.GetRow(0).CreateCell(0).SetCellValue("库位");
                        addrBatch.GetRow(0).CreateCell(1).SetCellValue("货号");
                        addrBatch.GetRow(0).CreateCell(2).SetCellValue("菜名");
                        addrBatch.GetRow(0).CreateCell(3).SetCellValue("净含量");
                        for (int f = 0; f < listgb.Count; f++)
                        {
                            addrBatch.GetRow(0).CreateCell(4 + f).SetCellValue("第" + listgb[f].BATCHNO.ToString() + "批");
                        }
                        addrBatch.GetRow(0).CreateCell(4 + listgb.Count).SetCellValue("总数");
                    }
                    else
                    {
                        int sumNumber = 0;
                        addrBatch.GetRow(k).CreateCell(0).SetCellValue(listBatchDetail[k - 1].LOOKUP);
                        addrBatch.GetRow(k).CreateCell(1).SetCellValue(listBatchDetail[k - 1].GOODSCODE);
                        addrBatch.GetRow(k).CreateCell(2).SetCellValue(listBatchDetail[k - 1].GOODSNAME);
                        addrBatch.GetRow(k).CreateCell(3).SetCellValue(listBatchDetail[k - 1].GOODSSPEC);
                        int number = listgb.Count + 1;

                        #region 判断显示多少列

                        if (4 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(4).SetCellValue(listBatchDetail[k - 1].BATCH_1);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_1;

                        }
                        if (5 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(5).SetCellValue(listBatchDetail[k - 1].BATCH_2);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_2;

                        }
                        if (6 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(6).SetCellValue(listBatchDetail[k - 1].BATCH_3);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_3;

                        }
                        if (7 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(7).SetCellValue(listBatchDetail[k - 1].BATCH_4);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_4;

                        }
                        if (8 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(8).SetCellValue(listBatchDetail[k - 1].BATCH_5);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_5;

                        }
                        if (9 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(9).SetCellValue(listBatchDetail[k - 1].BATCH_6);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_6;

                        }
                        if (10 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(10).SetCellValue(listBatchDetail[k - 1].BATCH_4);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_4;

                        }
                        if (11 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(11).SetCellValue(listBatchDetail[k - 1].BATCH_8);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_8;

                        }
                        if (12 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(12).SetCellValue(listBatchDetail[k - 1].BATCH_9);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_9;

                        }
                        if (13 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(13).SetCellValue(listBatchDetail[k - 1].BATCH_10);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_10;

                        }
                        if (14 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(14).SetCellValue(listBatchDetail[k - 1].BATCH_11);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_11;

                        }
                        if (15 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(15).SetCellValue(listBatchDetail[k - 1].BATCH_12);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_12;

                        }
                        if (16 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(16).SetCellValue(listBatchDetail[k - 1].BATCH_13);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_13;

                        }
                        if (17 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(17).SetCellValue(listBatchDetail[k - 1].BATCH_14);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_14;

                        }
                        if (18 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(18).SetCellValue(listBatchDetail[k - 1].BATCH_15);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_15;

                        }
                        if (19 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(19).SetCellValue(listBatchDetail[k - 1].BATCH_16);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_16;

                        }
                        if (20 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(20).SetCellValue(listBatchDetail[k - 1].BATCH_14);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_17;

                        }
                        if (21 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(21).SetCellValue(listBatchDetail[k - 1].BATCH_18);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_18;

                        }
                        if (22 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(22).SetCellValue(listBatchDetail[k - 1].BATCH_19);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_19;

                        }
                        if (23 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(23).SetCellValue(listBatchDetail[k - 1].BATCH_20);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_20;

                        }
                        if (24 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(24).SetCellValue(listBatchDetail[k - 1].BATCH_21);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_21;

                        }
                        if (25 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(25).SetCellValue(listBatchDetail[k - 1].BATCH_22);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_22;

                        }
                        if (26 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(26).SetCellValue(listBatchDetail[k - 1].BATCH_23);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_23;

                        }
                        if (27 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(27).SetCellValue(listBatchDetail[k - 1].BATCH_24);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_24;

                        }
                        if (28 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(28).SetCellValue(listBatchDetail[k - 1].BATCH_25);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_25;

                        }
                        if (29 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(29).SetCellValue(listBatchDetail[k - 1].BATCH_26);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_26;

                        }
                        if (30 < 4 + listgb.Count)
                        {
                            addrBatch.GetRow(k).CreateCell(30).SetCellValue(listBatchDetail[k - 1].BATCH_24);
                            sumNumber = sumNumber + listBatchDetail[k - 1].BATCH_27;

                        }
                        #endregion
                        addrBatch.GetRow(k).CreateCell(4 + listgb.Count).SetCellValue(sumNumber);



                    }
                }
                #endregion

                #region 加工单
                Hashtable hshTable = new Hashtable(); //  创建哈希表 
                for (int k = 0; k < listReceiveMachScheme.Count + 1; k++)
                {
                    addrMachScheme.CreateRow(k);
                    addrMachScheme.GetRow(k).Height = 400;

                    if (k == 0)
                    {
                        addrMachScheme.GetRow(0).CreateCell(0).SetCellValue("库位");
                        addrMachScheme.GetRow(0).CreateCell(1).SetCellValue("货号");
                        addrMachScheme.GetRow(0).CreateCell(2).SetCellValue("品名");
                        addrMachScheme.GetRow(0).CreateCell(3).SetCellValue("原材料库位");
                        addrMachScheme.GetRow(0).CreateCell(4).SetCellValue("主、配、原料");
                        addrMachScheme.GetRow(0).CreateCell(5).SetCellValue("半成品单位");
                        addrMachScheme.GetRow(0).CreateCell(6).SetCellValue("请购份数");

                        addrMachScheme.GetRow(0).CreateCell(7).SetCellValue("成品净含量");
                        addrMachScheme.GetRow(0).CreateCell(8).SetCellValue("配方需要数量");
                        addrMachScheme.GetRow(0).CreateCell(9).SetCellValue("每份量/克(净)");
                        addrMachScheme.GetRow(0).CreateCell(10).SetCellValue("生成总量(净)");
                        addrMachScheme.GetRow(0).CreateCell(11).SetCellValue("差异总量(净)");
                        addrMachScheme.GetRow(0).CreateCell(12).SetCellValue("档口");



                    }
                    else if (k > 0)
                    {

                        addrMachScheme.GetRow(k).CreateCell(0).SetCellValue(listReceiveMachScheme[k - 1].LOOKUP);
                        addrMachScheme.GetRow(k).CreateCell(1).SetCellValue(listReceiveMachScheme[k - 1].GOODSCODE);
                        addrMachScheme.GetRow(k).CreateCell(2).SetCellValue(listReceiveMachScheme[k - 1].GOODSNAME);
                        addrMachScheme.GetRow(k).CreateCell(3).SetCellValue(listReceiveMachScheme[k - 1].CUSTOMLOOKUP);
                        addrMachScheme.GetRow(k).CreateCell(4).SetCellValue(listReceiveMachScheme[k - 1].DGOODSNAME);
                        addrMachScheme.GetRow(k).CreateCell(5).SetCellValue(listReceiveMachScheme[k - 1].DPURUNITNAME);
                        addrMachScheme.GetRow(k).CreateCell(6).SetCellValue(Convert.ToInt32(listReceiveMachScheme[k - 1].ESTIMATENUMBER));
                        addrMachScheme.GetRow(k).CreateCell(7).SetCellValue(listReceiveMachScheme[k - 1].GOODSSPEC);
                        addrMachScheme.GetRow(k).CreateCell(8).SetCellValue(Convert.ToDouble(listReceiveMachScheme[k - 1].QTY));

                        addrMachScheme.GetRow(k).CreateCell(9).SetCellValue(Convert.ToDouble(listReceiveMachScheme[k - 1].DPKGLOSSQTY));
                        addrMachScheme.GetRow(k).CreateCell(10).SetCellValue(Convert.ToDouble(listReceiveMachScheme[k - 1].DPKGLOSSQTY * listReceiveMachScheme[k - 1].ESTIMATENUMBER));
                        addrMachScheme.GetRow(k).CreateCell(11).SetCellValue(Convert.ToDouble(listReceiveMachScheme[k - 1].DPKGLOSSQTY * listReceiveMachScheme[k - 1].ACTUALNUMBER));
                        addrMachScheme.GetRow(k).CreateCell(12).SetCellValue(listReceiveMachScheme[k - 1].GOODSDOOR);





                        if (k > 1)
                        {
                            string staCount = "";
                            int endCount = 0;
                            if (listReceiveMachScheme[k - 1].GOODSCODE.ToString() == listReceiveMachScheme[k - 2].GOODSCODE.ToString())
                            {
                                if (!hshTable.Contains(listReceiveMachScheme[k - 2].GOODSCODE.ToString()))
                                {
                                    hshTable.Add(listReceiveMachScheme[k - 2].GOODSCODE.ToString(), (k - 1).ToString());
                                }

                                endCount = k;
                                staCount = (string)hshTable[listReceiveMachScheme[k - 1].GOODSCODE.ToString()].ToString();
                                CellRangeAddress region = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 1, 1);
                                CellRangeAddress region1 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 2, 2);
                                CellRangeAddress region2 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 6, 6);
                                CellRangeAddress region3 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 7, 7);
                                addrMachScheme.AddMergedRegion(region);
                                addrMachScheme.AddMergedRegion(region1);
                                addrMachScheme.AddMergedRegion(region2);
                                addrMachScheme.AddMergedRegion(region3);

                            }
                            else
                            {
                                endCount = k - 1;
                                if (hshTable.Contains(listReceiveMachScheme[k - 2].GOODSCODE.ToString()))
                                {
                                    string aa = listReceiveMachScheme[k - 1].GOODSCODE.ToString();
                                    staCount = (string)hshTable[listReceiveMachScheme[k - 2].GOODSCODE.ToString()].ToString();
                                    CellRangeAddress region = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 1, 1);
                                    CellRangeAddress region1 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 2, 2);
                                    CellRangeAddress region2 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 6, 6);
                                    CellRangeAddress region3 = new CellRangeAddress(Convert.ToInt32(staCount), endCount, 7, 7);
                                    addrMachScheme.AddMergedRegion(region);
                                    addrMachScheme.AddMergedRegion(region1);
                                    addrMachScheme.AddMergedRegion(region2);
                                    addrMachScheme.AddMergedRegion(region3);

                                    if (!hshTable.Contains(listReceiveMachScheme[k - 1].GOODSCODE.ToString()))
                                    {
                                        hshTable.Add(listReceiveMachScheme[k - 1].GOODSCODE.ToString(), k.ToString());
                                    }
                                }
                            }
                        }

                    }
                }

                #endregion
            }
            #endregion

            // 写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return File(ms, "application/vnd.ms-excel", NewTimeStamp("批次订单"));
        }

    }
}
